#!/usr/bin/env python
# -*- coding: utf-8 -*-
from string import Template
from tack.lib import json_app, content_headers, status_codes
from datetime import date, datetime, timedelta
from time import strftime
from report_results import makeColumnList, makeDataList, replaceWithDictValues
from tack_ocie.api.util import state_def, build_row_dict


class Sql:
    select_by_id = """
    SELECT
        CASE 
            WHEN parent_id IS NULL
                THEN schedule_id
            ELSE parent_id
        END AS schedule_id,
        start,
        finish,
        poc_email,
        poc_phone,
        comments,
        unit_id,
        appointment_type_id,
        walkin,
        noshow,
        creator
    FROM ocie_schedule
    WHERE (schedule_id = $schedule_id AND state_finish IS NULL)
        OR (parent_id = $schedule_id AND state_finish IS NULL)
    """


    select_min_by_date = """
    SELECT  os.schedule_id,
            os.start,
            os.finish,
            CASE
                WHEN osa.count > 1
                    THEN ou.description + ' (' +
                         CAST(osa.count AS varchar) + ')'
                ELSE 
                    CASE
                        WHEN LTRIM(RTRIM(osa.last_name)) <> ''
                            THEN osa.last_name
                        ELSE 'Unknown'
                    END
            END AS description,
            oat.css_class AS css_class
    FROM
    (
        SELECT
            CASE 
                WHEN parent_id IS NULL
                    THEN schedule_id
                ELSE parent_id
            END AS schedule_id,
            start,
            finish,
            unit_id,
            appointment_type_id
        FROM ocie_schedule
        WHERE CONVERT(CHAR(10),start,121) = '$working_date'
            AND state_finish IS NULL
            AND state_key = $resourced_state_key
    ) AS os
    LEFT OUTER JOIN
    (
        SELECT COUNT(schedule_attendee_id) AS count,
               schedule_id,
               MAX(last_name) AS last_name
        FROM ocie_schedule_attendee
        GROUP BY schedule_id
    ) AS osa
        ON os.schedule_id = osa.schedule_id
    LEFT OUTER JOIN ocie_unit AS ou
        ON os.unit_id = ou.unit_id
    LEFT OUTER JOIN ocie_appointment_type AS oat
        ON os.appointment_type_id = oat.appointment_type_id
    ORDER BY os.start ASC, os.finish DESC
    """


    select_by_date = """
    SELECT  os.schedule_id,
            oat.description AS appointment_type,
            os.poc_phone,
            os.poc_email,
            CONVERT(CHAR(2),os.start,108) + 
            SUBSTRING( CONVERT(CHAR(5),os.start,108), 4, 2) AS start,
            CONVERT(CHAR(2),os.finish,108) + 
            SUBSTRING( CONVERT(CHAR(5),os.finish,108), 4, 2) AS finish,
            CASE
                WHEN osa.count > 1
                    THEN ou.description + ' (' +
                         CAST(osa.count AS varchar) + ')'
                ELSE 
                    CASE
                        WHEN LTRIM(RTRIM(osa.last_name)) <> ''
                            THEN osa.rank + ' ' + osa.last_name
                        ELSE 'Unknown'
                    END
            END AS description
    FROM
    (
        SELECT
            CASE 
                WHEN parent_id IS NULL
                    THEN schedule_id
                ELSE parent_id
            END AS schedule_id,
            start,
            finish,
            poc_phone,
            poc_email,
            unit_id,
            appointment_type_id
        FROM ocie_schedule
        WHERE CONVERT(CHAR(10),start,121) = '$working_date'
            AND state_finish IS NULL
            AND state_key = $state_key
    ) AS os
    LEFT OUTER JOIN
    (
        SELECT COUNT(schedule_attendee_id) AS count,
               schedule_id,
               MAX(last_name) AS last_name,
               MAX(ranks.abbreviation) AS rank
        FROM ocie_schedule_attendee
        LEFT OUTER JOIN ranks
            ON ocie_schedule_attendee.rank = ranks.id
        GROUP BY schedule_id
    ) AS osa
        ON os.schedule_id = osa.schedule_id
    LEFT OUTER JOIN ocie_unit AS ou
        ON os.unit_id = ou.unit_id
    LEFT OUTER JOIN ocie_appointment_type AS oat
        ON os.appointment_type_id = oat.appointment_type_id
    ORDER BY os.start ASC, os.finish DESC
    """


    select_min_by_state_key = """
    SELECT  os.schedule_id,
            UPPER(CONVERT(CHAR(11),os.start,113)) + ' ' +
            CONVERT(CHAR(2),os.start,108) + 
            SUBSTRING( CONVERT(CHAR(5),os.start,108), 4, 2) + ' - ' +
            CASE
                WHEN osa.count > 1
                    THEN ou.description + ' (' +
                         CAST(osa.count AS varchar) + ')'
                ELSE 
                    CASE
                        WHEN LTRIM(RTRIM(osa.last_name)) <> ''
                            THEN osa.rank + ' ' + osa.last_name
                        ELSE 'Unknown'
                    END
            END AS description
    FROM
    (
        SELECT
            CASE 
                WHEN parent_id IS NULL
                    THEN schedule_id
                ELSE parent_id
            END AS schedule_id,
            start,
            finish,
            unit_id
        FROM ocie_schedule
        WHERE state_finish IS NULL
            AND state_key = $state_key
    ) AS os
    LEFT OUTER JOIN
    (
        SELECT COUNT(schedule_attendee_id) AS count,
               schedule_id,
               MAX(last_name) AS last_name,
               MAX(ranks.abbreviation) AS rank
        FROM ocie_schedule_attendee
        LEFT OUTER JOIN ranks
            ON ocie_schedule_attendee.rank = ranks.id
        GROUP BY schedule_id
    ) AS osa
        ON os.schedule_id = osa.schedule_id
    LEFT OUTER JOIN ocie_unit AS ou
        ON os.unit_id = ou.unit_id
    ORDER BY os.start ASC, os.finish DESC
    """


    select_canceled = """
    SELECT  os.schedule_id,
            oat.description AS appointment_type,
            os.poc_phone,
            os.poc_email,
            UPPER(CONVERT(CHAR(11),os.start,113)) AS date,
            CONVERT(CHAR(2),os.start,108) + 
            SUBSTRING( CONVERT(CHAR(5),os.start,108), 4, 2) AS start,
            CONVERT(CHAR(2),os.finish,108) + 
            SUBSTRING( CONVERT(CHAR(5),os.finish,108), 4, 2) AS finish,
            CASE
                WHEN osa.count > 1
                    THEN ou.description + ' (' +
                         CAST(osa.count AS varchar) + ')'
                ELSE 
                    CASE
                        WHEN LTRIM(RTRIM(osa.last_name)) <> ''
                            THEN osa.rank + ' ' + osa.last_name
                        ELSE 'Unknown'
                    END
            END AS description
    FROM
    (
        SELECT TOP 20
            CASE 
                WHEN parent_id IS NULL
                    THEN schedule_id
                ELSE parent_id
            END AS schedule_id,
            start,
            finish,
            poc_phone,
            poc_email,
            unit_id,
            appointment_type_id
        FROM ocie_schedule
        WHERE state_finish IS NULL
            AND state_key = $state_key
        ORDER BY state_start DESC
    ) AS os
    LEFT OUTER JOIN
    (
        SELECT COUNT(schedule_attendee_id) AS count,
               schedule_id,
               MAX(last_name) AS last_name,
               MAX(ranks.abbreviation) AS rank
        FROM ocie_schedule_attendee
        LEFT OUTER JOIN ranks
            ON ocie_schedule_attendee.rank = ranks.id
        GROUP BY schedule_id
    ) AS osa
        ON os.schedule_id = osa.schedule_id
    LEFT OUTER JOIN ocie_unit AS ou
        ON os.unit_id = ou.unit_id
    LEFT OUTER JOIN ocie_appointment_type AS oat
        ON os.appointment_type_id = oat.appointment_type_id
    ORDER BY os.start ASC, os.finish DESC
    """


    select_attendees = """
    SELECT ocie_schedule_attendee.last_name,
           ocie_schedule_attendee.first_name,
           ocie_schedule_attendee.ssn,
           ocie_schedule_attendee.rank,
           ranks.abbreviation AS rank_abbreviation
    FROM ocie_schedule_attendee
    LEFT OUTER JOIN ranks
        ON ocie_schedule_attendee.rank = ranks.id
    WHERE schedule_id = $schedule_id
    ORDER BY schedule_attendee_id
    """
    
    select_json_schedule = """
    SELECT start as Start, finish as Finish, unit_description as Unit, 
           appointment_description as Description, total_attendee as Attendees,
           poc_phone as Phone
    FROM ocie_json_shedule
    WHERE (start > '$start') AND (finish < '$finish')
    ORDER BY Start
    """


def get_by_id(environ, start_response):
    schedule_id = environ['dispatcher.groups']['schedule_id']
    sql = Sql.select_by_id
    sql = Template(sql).substitute(locals())
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    row_cd = cursor.description
    if rows:
        output = build_row_dict(row_cd, rows[0])
        schedule_id = output['schedule_id']
        sql = Sql.select_attendees
        sql = Template(sql).substitute(locals())
        cursor.execute(sql)
        attendee_rows = cursor.fetchall()
        attendee_list = []
        attendee_cd = cursor.description
        for attendee in attendee_rows:
            attendee_dict = build_row_dict(attendee_cd, attendee)
            attendee_list.append(attendee_dict)
        output['attendees'] = attendee_list
    else:
        start_response(status_codes.not_found, content_headers.default_plain())
        return ['404 Not Found']
    return json_app(output)(environ, start_response)    


def get_by_date(environ, start_response):
    try:
        throwaway = environ['dispatcher.groups']['start']
        start_date = datetime.strptime(throwaway,'%Y-%m-%d')
        throwaway = environ['dispatcher.groups']['finish']
        finish_date = datetime.strptime(throwaway,'%Y-%m-%d')
    except:
        start_response(status_codes.error, content_headers.default_plain())
        return ['Error']
    cursor = environ['connection'].cursor()
    output = {}
    resourced_state_key = state_def.RESOURCED
    for day_diff in range(0, (finish_date - start_date).days + 1):
        working_date = start_date + timedelta(days=day_diff)
        working_date = working_date.strftime('%Y-%m-%d')
        day = {}
        sql = Sql.select_min_by_date
        sql = Template(sql).substitute(locals())
        cursor.execute(sql)
        rows = cursor.fetchall()
        output[working_date] = build_day(cursor.description, rows)
    return json_app(output)(environ, start_response)
    
    
def get_json_by_date(environ, start_response):
    try:
        throwaway = environ['dispatcher.groups']['start']
        start = datetime.strptime(throwaway,'%Y-%m-%d')
        throwaway = environ['dispatcher.groups']['finish']
        finish = datetime.strptime(throwaway,'%Y-%m-%d')
    except:
        start_response(status_codes.error, content_headers.default_plain())
        return ['Error']
    sql = Sql.select_json_schedule
    sql = Template(sql).substitute(locals())
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    if rows:
        output = {}
        output['title'] = 'OCIE Scheduled Appointments from ' + start.strftime('%Y-%m-%d') + ' to ' + finish.strftime('%Y-%m-%d')
        output['name'] = rows[0].Description + '.pdf'
        cursor = environ['connection'].cursor()
        cursor.execute(sql)
        rows = cursor.fetchall()
        output['sections'] = []
        subsection = {}
        subsection['title'] = ''
        subsection['table'] = {}
        subsection['table']['headers'] = makeColumnList(cursor.description)
        subsection['table']['rows'] = []
        for row in rows:
            subsection['table']['rows'].append(makeDataList(cursor.description, row))
        output['sections'].append(subsection)
    else:
        start_response(status_codes.not_found, content_headers.default_plain())
        return ['404 Not Found']
    return json_app(output)(environ, start_response)


def get_tasks(environ, start_response):
    cursor = environ['connection'].cursor()
    output = {'rescheduled': [], 'drafted': [], 'today': []}
    state_key = state_def.RESCHEDULED
    items = []
    sql = Sql.select_min_by_state_key
    sql = Template(sql).substitute(locals())
    cursor.execute(sql)
    rows = cursor.fetchall()
    for row in rows:
        items.append(build_row_dict(cursor.description, row))
    output['rescheduled'] = items
    state_key = state_def.DRAFTED
    items = []
    sql = Sql.select_min_by_state_key
    sql = Template(sql).substitute(locals())
    cursor.execute(sql)
    rows = cursor.fetchall()
    for row in rows:
        items.append(build_row_dict(cursor.description, row))
    output['drafted'] = items
    state_key = state_def.RESOURCED
    working_date = date.today().strftime('%Y-%m-%d')
    items = []
    sql = Sql.select_by_date
    sql = Template(sql).substitute(locals())
    cursor.execute(sql)
    rows = cursor.fetchall()
    for row in rows:
        items.append(build_row_dict(cursor.description, row))
    output['today'] = items
    return json_app(output)(environ, start_response)


def get_canceled(environ, start_response):
    cursor = environ['connection'].cursor()
    state_key = state_def.CANCELED
    output = []
    sql = Sql.select_canceled
    sql = Template(sql).substitute(locals())
    cursor.execute(sql)
    rows = cursor.fetchall()
    row_cd = cursor.description
    for row in rows:
        appt = build_row_dict(row_cd, row)
        schedule_id = appt['schedule_id']
        sql = Sql.select_attendees
        sql = Template(sql).substitute(locals())
        cursor.execute(sql)
        attendee_rows = cursor.fetchall()
        attendee_list = []
        attendee_cd = cursor.description
        for attendee in attendee_rows:
            attendee_dict = build_row_dict(attendee_cd, attendee)
            if attendee_dict['last_name']:
                attendee_list.append(attendee_dict['last_name'])
        appt['attendees'] = ', '.join(attendee_list)
        output.append(appt)
    return json_app(output)(environ, start_response)


def build_day(cd, rows):
    output = {'appts': [], 'extended': False, 'max_simul': 0}
    appts = []
    simul = [0 for ind in range(0,36)]
    for row in rows:
        appt = build_row_dict(cd, row)
        start = appt['start'][11:13] + appt['start'][14:16]
        finish = appt['finish'][11:13] + appt['finish'][14:16]
        if start < '0730' or finish > '1630':
            output['extended'] = True
        if start >= '1630' or finish <= '0730':
            pass
        else:
            if start < '0730':
                start = '0730'
            if finish > '1630':
                finish = '1630'
            start = int(start[0:2]) * 60 + int(start[2:4])
            finish = int(finish[0:2]) * 60 + int(finish[2:4])
            appt['vert_length'] = length = (finish - start) / 15
            appt['vert_pos'] = pos = (start - 450) / 15;
            del appt['start'], appt['finish']
            for ind in range(pos, (pos + length)):
                simul[ind] += 1
            appts.append(appt)
    appts = sorted(appts, compare_pos_and_length)
    output['appts'] = appts
    output['max_simul'] = max(simul)
    return output


def compare_pos_and_length(a, b):
    a_pos, b_pos = a['vert_pos'], b['vert_pos']
    a_len, b_len = a['vert_length'], b['vert_length']    
    return cmp(a_pos, b_pos) or cmp(b_len, a_len)
